/*
 * DataBaseManager.java
 *
 * Created on 29 de Mar�o de 2006, 20:11
 *
 * To change this template, choose Tools | Template Manager
 * and open the template in the editor.
 */

package hubsniffer.db;

import libjdc.dc.client.messages.ExceptionMessage;
import hubsniffer.DCHub;
import libjdc.dc.client.messages.RawMessage;
import libjdc.dc.client.User;
import libjdc.dc.client.UserList;
import hubsniffer.db.util.Util;
import hubsniffer.hublist.db.HublistWriter;
import libjdc.dc.client.HubClient;
import libjdc.dc.log.Log;
import java.io.UnsupportedEncodingException;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.text.MessageFormat;
import java.util.Date;
import java.util.Iterator;
import java.util.LinkedList;

/**
 *
 * @author botelhodaniel
 */
public class DataBaseManager {
    
    private static final MessageFormat SQL_INSERT_HUB = new MessageFormat(
            "INSERT INTO \"hub\" (" +
            "\"hub_id\", " +
            "\"hub_address\", " +
            "\"hub_port\"," +
            "\"hub_user_count\"," +
            "\"rating_id\"," +
            "\"hub_relyability\"," +
            "\"hub_owner_id\"," +
            "\"software_id\"," +
            "\"hub_min_share\"," +
            "\"hub_min_slots\"," +
            "\"hub_max_users\"," +
            "\"hub_max_hubs\"," +
            "\"descricao\"," +
            "\"location_id\") VALUES ({0}, '{1}', '{2}','{3}','{4}','{5}','{6}','{7}','{8}','{9}','{10}','{11}','{12}','{13}')");
    
    private Connection       conn;        // A connection to the database
    private Statement        sql_stm;
    private ResultSet rs;
    
    public DataBaseManager(DBInfo db) throws ClassNotFoundException, SQLException {
        this(db.getDatabase(),db.getUsername(),db.getPassword());
    }
    /** Creates a new instance of DataBaseManager
     * @throws ClassNotFoundException
     * @throws SQLException */
    public DataBaseManager(String database,String username,String password) throws ClassNotFoundException, SQLException {
        Class.forName("org.postgresql.Driver");
        conn = DriverManager.getConnection(database,username, password);
        sql_stm = getConn().createStatement();
        
        sql_stm.setEscapeProcessing(true);
    }
    
    public LinkedList<HubClient> getHubClients() throws SQLException{
        return new HubClientReader(this).getHubClientList();
    }
    
    
    public Iterator<DCHub> getAllHubs() throws SQLException, ClassNotFoundException{
        return new HubListReader(this).getHubListNacional().iterator();
    }
    
    public void update(DCHub hub,int statistic_id) throws SQLException{
        String message = "UPDATE hub SET statistic_id="+statistic_id+", hub_name='"+Util.escapeString(hub.getHubName())+"' WHERE hub_id="+hub.getHub_id();
        Log.out.println(message);
        Log.out.println(getSql_stm().executeUpdate(message));
    }
    
    public void writeUpdate(String query) throws SQLException, UnsupportedEncodingException{
        getSql_stm().executeUpdate(new String(query.getBytes("ISO-8859-1"),"ISO-8859-1"));
    }
    
    public void insertHub(String address,String port){
        String toInsert =  "INSERT INTO \"hub\" (\"hub_id\", \"hub_address\", \"hub_port\") VALUES (nextval('public.hub_hub_id_seq'::text), '"+address+"', '"+port+"')";
    }
    
    public void addRating(String denominacao,String rating_img) throws SQLException{
        String message =  "INSERT INTO \"rating\" (\"rating_id\", \"denominacao\", \"rating_img\") VALUES (nextval('public.rating_rating_id_seq'::text), '"+denominacao+"', '"+rating_img+"')";
        Log.out.println(message);
        getSql_stm().execute(message);
    }
    
    public void addStatistic(LinkedList<DCHub> hubList) {
        Iterator<DCHub> it = hubList.iterator();
        
        try {
            emptyTable("dc_user");
            emptyTable("raw_msg");
            emptyTable("exception_msg");
        } catch (SQLException ex) {
            ex.printStackTrace(Log.err);
        }
        
        while(it.hasNext()){
            try {
                addStatistic(it.next());
            } catch (SQLException ex) {
                ex.printStackTrace(Log.err);
            }
        }
    }
    
    public void addStatistic(DCHub hub) throws SQLException{
        int statistic_id = 0;
        Date date = hub.getHubSession().getStart();
        String MTOD ="";
        int user_count = hub.getHubSession().getUserList().size();
        String statistic_share = hub.getHubSession().getUserList().getUserShare()+"";
        int hub_id = hub.getHub_id();
        int software_id = (hub.getHubSession().getHubClient()== null) ? 0: hub.getHubSession().getHubClient().getSoftware_id();
        String is_online = (hub.getHubSession().isIs_online()? "TRUE":"FALSE");
        
        String message = "INSERT INTO \"statistic\" (\"date\", \"is_online\", \"info\", \"user_count\", \"hub_id\", \"software_id\", \"statistic_share\") VALUES (" +
                "'"+date+"', "+is_online+", '"+MTOD+"', '"+user_count+"', '"+hub_id+"', '"+software_id+"', '"+ statistic_share + "')";
        Log.out.println(message);
        getSql_stm().executeUpdate(message);
        statistic_id = getAvailableStatisticId();
        
        /*
         * Depois insere os DC_Users
         */
        addUserList(statistic_id,hub.getHubSession().getUserList());
        
        /*
         * Depois insere os RawMessages
         */
        addRawMessages(statistic_id,hub.getHubSession().getRaw_messages());
        
        /*
         * Depois insere os ExceptionMessage
         */
        addExceptionMessages(statistic_id,hub.getHubSession().getException_messages());
        
        /*
         * Actualiza o hub
         */
        update(hub,statistic_id);
    }
    
    /**
     *  Retorna um ID disponivel na bd
     */
    private int getAvailableStatisticId() throws SQLException{
        ResultSet rs = this.getSql_stm().executeQuery("SELECT MAX(statistic_id) FROM statistic");
        
        rs.next();
        
        return rs.getInt(1);
    }
    
    
    private void addExceptionMessages(int statistic_id,LinkedList<ExceptionMessage> exceptionMessages) throws SQLException{
        Iterator<ExceptionMessage> it = exceptionMessages.iterator();
        
        while(it.hasNext()){
            ExceptionMessage next = it.next();
            String message = "INSERT INTO \"exception_msg\" (\"exception_msg_date\", \"exception_msg_trace\", \"statistic_id\") VALUES (" +
                    "'"+next.getData()+"', '"+Util.escapeString(next.getTrace())+"', '"+statistic_id+"')";
            Log.out.println(message);
            Log.out.println(getSql_stm().executeUpdate(message));
        }
    }
    
    private void addRawMessages(int statistic_id,LinkedList<RawMessage> rawMessages) throws SQLException{
        Iterator<RawMessage> it = rawMessages.iterator();
        
        while(it.hasNext()){
            RawMessage next = it.next();
            String message = "INSERT INTO \"raw_msg\" (\"raw_msg_date\", \"raw_msg_from\", \"raw_msg_to\", \"raw_msg_message\", \"statistic_id\") VALUES (" +
                    "'"+next.getDate()+"', '"+next.getFrom()+"', '"+next.getTo()+"', '"+Util.escapeString(next.getMessage())+"', '"+statistic_id+"')";
            Log.out.println(message);
            Log.out.println(getSql_stm().executeUpdate(message));
        }
    }
    
    private void addUserList(int statistic_id,UserList userList) throws SQLException{
        Iterator<User> it = userList.iterator();
        
        while(it.hasNext()){
            User next = it.next();
            String message = "INSERT INTO \"dc_user\" (\"dc_user_nick\", \"dc_user_description\", \"dc_user_speed\", \"dc_user_email\", \"dc_user_share\", \"statistic_id\", \"dc_user_ip\") VALUES (" +
                    "'"+Util.escapeString(next.getUserInfo().getNick())+"', '"+Util.escapeString(next.getUserInfo().getDescription())+"', '"+next.getUserInfo().getSpeed()+"', '"+Util.escapeString(next.getUserInfo().getEmail())+"', '"+next.getUserInfo().getSharedBytes()+"', '"+statistic_id+"', '"+next.getHost()+"')";
            Log.out.println(message);
            Log.out.println(getSql_stm().executeUpdate(message));
        }
    }
    
    private int getHubId(String address,int port) throws SQLException{
        rs = getSql_stm().executeQuery("SELECT hub.hub_id from hub WHERE hub.hub_address = '"+address+"' AND hub.hub_port = "+port);
        getRs().next();
        return getRs().getInt(1);
    }
    
    public ResultSet writeQuery(String query) throws SQLException, UnsupportedEncodingException{
        return getSql_stm().executeQuery(new String(query.getBytes("ISO-8859-1"),"ISO-8859-1"));
    }
    
    public boolean containsHubInfo(int hub_id) throws SQLException{
        rs = getSql_stm().executeQuery("SELECT * from hub_info WHERE hub_id = "+hub_id);
        return getRs().next();
        //return getRs().getInt(1);
    }
    
    public User getSnifferUserFromHub(int hub_id) throws SQLException{
        
        rs = getSql_stm().executeQuery("select * from sniffer_user,hub where hub.sniffer_user_id = sniffer_user.sniffer_user_id AND hub.hub_id ="+hub_id);
        
        if (rs.next()) {
            return new User(false,rs.getString("sniffer_user_nick"),rs.getString("sniffer_user_description"),rs.getString("sniffer_user_speed"),rs.getString("sniffer_user_email"),new Long(rs.getString("sniffer_user_sharedBytes")).longValue(),rs.getString("sniffer_user_passwd"));
        }
        return null;
    }
    
    public void closeDB() throws SQLException{
        getConn().close();
    }
    
    public Connection getConn() {
        return conn;
    }
    
    public Statement getSql_stm() {
        return sql_stm;
    }
    
    public ResultSet getRs() {
        return rs;
    }
    
    public void emptyTable(String table_name) throws SQLException {
        String message = "DELETE FROM "+table_name;
        Log.out.println(message);
        getSql_stm().executeUpdate(message);
    }
    
    /**
     * Escreve uma linha de SQL na BD.
     * @param sql
     * @param iso
     * @throws java.io.UnsupportedEncodingException
     * @throws java.sql.SQLException
     */
    public synchronized void writeRawSql(String sql,boolean iso) throws UnsupportedEncodingException, SQLException{
        String latin1 = new String(sql.getBytes("ISO-8859-1"),"ISO-8859-1");
        Log.out.println(latin1);
        Log.out.println(this.getSql_stm().executeUpdate(iso? latin1 : sql));
    }
}
